MySQL · Advanced Cheatsheet | DDL, Constraints, Procedures & More

🗄️ CREATE & USE & DESCRIBE

CREATE DATABASE demodatabase;

USE demodatabase; -- select db to work

SHOW TABLES;

CREATE TABLE demotable (
  Id INT PRIMARY KEY AUTO_INCREMENT,
  Name VARCHAR(100) NOT NULL,
  Email VARCHAR(100) UNIQUE NOT NULL,
  Phone CHAR(10) NOT NULL
);

DESC demotable; -- describe table structure

⭐ PRIMARY KEY = NOT NULL + UNIQUE · only one per table.
FOREIGN KEY links tables (many allowed).

📐 normalization & relationships

Normalization reduces redundancy & improves integrity

🔹 1:1 — person ↔ pan card
🔹 1:N — school ↔ student
🔹 N:N — customers ↔ products (junction table)

CREATE TABLE department (
Id INT PRIMARY KEY, Name VARCHAR(100) NOT NULL );
CREATE TABLE employee(
Id INT PRIMARY KEY, Name VARCHAR(100), Salary INT, Department_id INT,
CONSTRAINT emp_depart FOREIGN KEY (Department_id) REFERENCES department(Id) );

🔑 PK unique id🔗 foreign key reference

📚 students dataset · real example

CREATE TABLE students (
student_id INT PRIMARY KEY, name VARCHAR(50), age INT, gender VARCHAR(10),
course VARCHAR(50), city VARCHAR(50), marks INT, admission_year INT );

-- 15 sample rows (Amit, Priya, Rahul...)
INSERT INTO students VALUES
(101,'Amit Sharma',21,'Male','Java','Mumbai',78,2023),
(102,'Priya Mehta',20,'Female','Python','Pune',85,2024),
(103,'Rahul Patil',22,'Male','Java','Mumbai',65,2022),
(104,'Sneha Joshi',21,'Female','Data Science','Delhi',92,2023);
SELECT * FROM students LIMIT 5;

📌 More rows: 15 total entries (Mumbai, Pune, Delhi etc) — check full dataset in footer.

✏️ ALTER table · column mastery

-- ADD column
ALTER TABLE student ADD COLUMN address VARCHAR(250) NOT NULL;

-- DROP column
ALTER TABLE student DROP COLUMN address;

-- ADD AFTER specific column
ALTER TABLE student ADD COLUMN Marks INT AFTER Email;

-- ADD FIRST
ALTER TABLE student ADD COLUMN Course_Id INT FIRST;

-- RENAME column
ALTER TABLE student RENAME COLUMN DOB TO Date_of_Birth;

-- MODIFY datatype
ALTER TABLE student MODIFY COLUMN Age VARCHAR(100);

-- ADD CONSTRAINT (unique/check)
ALTER TABLE student ADD CONSTRAINT unique_email UNIQUE(Email);

⚠️ advanced constraints

CREATE TABLE check_constraints (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL, Phone CHAR(10) UNIQUE,
Email VARCHAR(50) UNIQUE NOT NULL, Age INT CHECK (Age > 18),
join_date DATE DEFAULT (CURRENT_DATE) );

-- Adding / dropping constraints via ALTER
ALTER TABLE customers ADD PRIMARY KEY(Id);
ALTER TABLE customers DROP PRIMARY KEY;
ALTER TABLE customers ADD CONSTRAINT chk_id CHECK (Id > 999);
ALTER TABLE customers DROP CONSTRAINT chk_id;

✅ CHECK🔁 DEFAULT🔒 UNIQUE🔄 AUTO_INCREMENT

📊 ORDER BY · GROUP BY · HAVING

-- ORDER BY (ASC/DESC)
SELECT * FROM students ORDER BY marks DESC;
SELECT * FROM students WHERE city = 'Mumbai' ORDER BY admission_year;

-- GROUP BY + aggregate
SELECT course, AVG(marks) as avg_marks FROM students GROUP BY course;

-- HAVING (filter groups)
SELECT city, COUNT(*) as total FROM students
GROUP BY city HAVING COUNT(*) > 2;

💡 HAVING works on grouped data, WHERE on raw rows.

🎯 pattern filters · LIKE / IN / BETWEEN

-- LIKE wildcards
SELECT * FROM students WHERE name LIKE "A%"; -- starts with A
SELECT * FROM students WHERE name LIKE "%a"; -- ends with a
SELECT * FROM students WHERE name LIKE "_a%"; -- second char = 'a'

-- IN & BETWEEN
SELECT * FROM students WHERE course_id IN (102,105,108);
SELECT * FROM students WHERE age BETWEEN 20 AND 22;
SELECT * FROM students LIMIT 5; -- first 5 rows
SELECT * FROM students LIMIT 1,3; -- skip 1, show 3

🔤 string & built-in functions

SELECT UPPER('mysql'), LOWER('LEARN');
SELECT name, LOWER(name) as lower_name FROM students;
SELECT CONCAT(name, ' - ', course) FROM students;
SELECT SUBSTRING('DATABASE', 1, 4); -- 'DATA'
SELECT name, LENGTH(name) FROM students WHERE LENGTH(name) > 10;
SELECT AVG(marks), SUM(marks), COUNT(*) FROM students;

📌 Aggregate: MIN, MAX, SUM, AVG, COUNT work great with GROUP BY

⚡ INDEX & VIEW

-- CREATE INDEX (faster search)
CREATE INDEX idx_students_city ON students(city, marks);
-- DROP INDEX
ALTER TABLE students DROP INDEX idx_students_city;
-- CREATE VIEW (simplify complex queries)
CREATE VIEW high_scorers AS SELECT name, course, marks FROM students WHERE marks > 85;
SELECT * FROM high_scorers;
DROP VIEW high_scorers;

🔍 Views are virtual tables — secure & reusable.

📦 STORED PROCEDURE · automation

DELIMITER //
CREATE PROCEDURE get_mumbai_students()
BEGIN
SELECT name, course, marks FROM students WHERE city = 'Mumbai' ORDER BY marks DESC;
END //
DELIMITER ;
CALL get_mumbai_students();
DROP PROCEDURE get_mumbai_students;

-- procedure with parameters (optional)
CREATE PROCEDURE filter_by_course(IN course_name VARCHAR(50))
BEGIN SELECT * FROM students WHERE course = course_name; END //
CALL filter_by_course('Java');

🏢 company schema · FK in action

CREATE DATABASE company;
USE company;
CREATE TABLE department (Id INT PRIMARY KEY, Name VARCHAR(100) NOT NULL);
CREATE TABLE employee (
Id INT PRIMARY KEY, Name VARCHAR(100), Salary INT, Department_id INT,
CONSTRAINT fk_dept FOREIGN KEY (Department_id) REFERENCES department(Id)
);
INSERT INTO department VALUES (1,'I.T'),(2,'H.R'),(3,'OPERATION');
INSERT INTO employee VALUES (101,'A',23000,1),(102,'B',45000,2),(103,'C',15000,3);
SELECT e.Name, d.Name FROM employee e JOIN department d ON e.Department_id = d.Id;

📋 quick lookup · constraints & clauses

Concept	Purpose
PRIMARY KEY	UNIQUE + NOT NULL, only one
FOREIGN KEY	References PK of another table
UNIQUE	No duplicate values
CHECK	Condition on values
DEFAULT	Fallback value if not provided
AUTO_INCREMENT	Auto-increment integer
GROUP BY	Groups rows for aggregation
HAVING	Filters after GROUP BY

✨ Many-to-many: bridge table with two foreign keys. Normalization avoids redundancy.

🐬 MySQL MASTERY · cheatsheet v2.0 · advanced